package LogicSide;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import DBside.JDBCConnection;
import DBside.JDBCConnection.Genres;
import DBside.TableItem;
import DBside.Tools;
import DBside.JDBCConnection.Table;

public class Recommendation {

	/**
	 * 
	 * @param genre_id
	 * @param Max
	 *            - the maximum number of results to return
	 * @param s
	 *            - the active session
	 * @return ArrayList with the recommended songs
	 * @throws SQLException
	 */
	public static ArrayList<SongDetails> RecommendSongToMe(int genre_id,
			int Max, Session s) throws SQLException {
		JDBCConnection con = s.getCon();

		ArrayList<SongDetails> lst = new ArrayList<SongDetails>();
		int mone = 0;
		TableItem ti1, ti2;
		ResultSet recomnderRS, s1 = null, s2 = null, songRS = null, genreRS = null;
		int my_id = s.getUser_id();

		con.getConnection().setAutoCommit(false);
		// check all the connections between me and the recommenders
		ti1 = new TableItem("userIdB", Tools.Type.INT, my_id);
		ti2 = new TableItem("genreId", Tools.Type.INT, genre_id);
		TableItem[] t3 = { ti1, ti2 };

		// sort by rating
		recomnderRS = con.select("userIdA", t3, Tools.Connector.AND, "rating",
				DBside.JDBCConnection.Table.RECOMMENDERS);

		// for each user recommends to me
		try {

			while (recomnderRS.next()) {
				TableItem[] firstCondTI = { new TableItem("user_id",
						Tools.Type.INT, recomnderRS.getInt("userIdA")) };
				s1 = con.select("song_id", firstCondTI, null, null,
						DBside.JDBCConnection.Table.USERVSSONG);

				// let's check all of his songs
				while (s1.next()) {
					TableItem[] c1 = { new TableItem("id", Tools.Type.INT,
							s1.getInt("song_id")) };

					songRS = con.select("genre", c1, null, null,
							DBside.JDBCConnection.Table.SONG);
					if (songRS.next()) {
						TableItem[] c2 = { new TableItem("id",
								Tools.Type.INT, songRS.getInt("genre")) };

						genreRS = con.select("parent_genre_id", c2, null, null,
								DBside.JDBCConnection.Table.GENRE);
						if (genreRS.next()
								&& genreRS.getInt("parent_genre_id") == genre_id) {
							TableItem[] secondCondTI = {
									new TableItem("user_id", Tools.Type.INT,
											my_id),
									new TableItem("song_id", Tools.Type.INT,
											s1.getInt("song_id")) };
							s2 = con.select("song_id", secondCondTI,
									Tools.Connector.AND, null,
									DBside.JDBCConnection.Table.USERVSSONG);

							// if he has a song that i don't have then I shall
							// add it.
							if (!s2.next()) {
								lst.add(ManageSongs.getSongDetails(s,
										s1.getInt("song_id")));
								mone++;
								if (mone == Max)
									return lst;
							}

						}
					}
				}
			}
			con.getConnection().commit();
		} catch (SQLException e) {
			if (con.getConnection() != null)
				con.getConnection().rollback();
			e.printStackTrace();
		} finally {
			if (recomnderRS != null)
				recomnderRS.close();
			if (s1 != null)
				s1.close();
			if (s2 != null)
				s2.close();
			if (songRS != null)
				songRS.close();
			if (genreRS != null)
				genreRS.close();
		}
		return lst;
	}

	/**
	 * updates the recommenders' list
	 * 
	 * @param s
	 * @throws SQLException
	 */
	public static void refreshRecommenders(Session s) throws SQLException {
		JDBCConnection con = s.getCon();
		int mone = 0, all = 0;
		ResultSet rs, s0 = null, s1 = null, s2 = null, songRS = null;
		TableItem ti1, ti2;
		int my_id = s.getUser_id();
		con.getConnection().setAutoCommit(false);

		// delete all of my recommenders
		TableItem[] tempTI = { new TableItem("userIdB", Tools.Type.INT, my_id) };
		con.delete(tempTI, null, Table.RECOMMENDERS);

		for (int i = 1; i < 12; i++) {
			int genreId = Genres.getGenreFromId(i).getId();
			rs = con.select("id", DBside.JDBCConnection.Table.USER);
			try {
				while (rs.next()) {
					if (rs.getInt("id") != my_id) {
						// for each song that the other likes check if I like it
						// either
						TableItem[] conditionTI1 = { new TableItem("user_id",
								Tools.Type.INT, rs.getInt("id")) };
						songRS = con.select("song_id", conditionTI1, null,
								null, DBside.JDBCConnection.Table.USERVSSONG);
						while (songRS.next()) {
							TableItem[] conditionTI2 = { new TableItem("id",
									Tools.Type.INT, songRS.getInt("song_id")) };
							s0 = con.select("genre", conditionTI2, null, null,
									DBside.JDBCConnection.Table.SONG);
							if (s0.next()) {
								TableItem[] conditionTI3 = { new TableItem(
										"id", Tools.Type.INT,
										s0.getInt("genre")) };
								s1 = con.select("parent_genre_id",
										conditionTI3, null, null,
										DBside.JDBCConnection.Table.GENRE);
								if (s1.next()
										&& s1.getInt("parent_genre_id") == genreId) {
									ti1 = new TableItem("user_id",
											Tools.Type.INT, my_id);
									ti2 = new TableItem("song_id",
											Tools.Type.INT,
											songRS.getInt("song_id"));
									TableItem[] t3 = { ti1, ti2 };
									s2 = con.select(
											"song_id",
											t3,
											Tools.Connector.AND,
											null,
											DBside.JDBCConnection.Table.USERVSSONG);
									if (s2.next()) {
										mone++;
									}
									all++;
								}
							}
						}

						// should be changed later-criterion
						if (all != 0) {
							double raf = ((double) mone) / all;
							TableItem[] insertTI = {
									new TableItem("userIdB", Tools.Type.INT,
											my_id),
									new TableItem("userIdA", Tools.Type.INT,
											rs.getInt("id")),
									new TableItem("genreId", Tools.Type.INT,
											genreId),
									new TableItem("rating", Tools.Type.DOUBLE,
											raf) };
							con.insert(
									DBside.JDBCConnection.Table.RECOMMENDERS,
									insertTI);
						}
						mone = 0;
						all = 0;
					}
				}
				con.getConnection().commit();
			} catch (SQLException e) {
				if (con.getConnection() != null)
					con.getConnection().rollback();
				e.printStackTrace();
			} finally {
				if (rs != null)
					rs.close();
				if (s1 != null)
					s1.close();
				if (s2 != null)
					s2.close();
				if (songRS != null)
					songRS.close();
				if (s0 != null)
					s0.close();
			}
		}
	}
}
